USE AdventureWorks2008

--Creating a Full Text Index

exec sp_fulltext_database 'enable'
EXEC sp_fulltext_catalog 'Cat_Name', 'create'
EXEC sp_fulltext_table 'Production.Product', 'create', 'Cat_Name', 'PK_Product_ProductID';
--Add some columns
EXEC sp_fulltext_column 'Production.Product','Name','add';
-- Activate the full-text index
EXEC sp_fulltext_table 'Production.Product','activate';
EXEC sp_fulltext_table 'Production.Product ', 'Start_change_tracking';
EXEC sp_fulltext_table 'Production.Product ', 'Start_background_updateindex';

--Querying a Full-Text Index using predicates

SELECT Name
FROM Production.Product
WHERE CONTAINS(Name, ' "Chain*" ');

--Querying a Full Text Index using functions

SELECT Product.ProductID, Product.Name, Keys.Rank
FROM Production.Product
    INNER JOIN FREETEXTTABLE(Production.Product, Name, 
        'Chain',LANGUAGE 'English',2) AS Keys
        ON Product.ProductID = Keys.[KEY];
